/*修改表信息*/
module.exports = class extends think.Model {
  /*获取表*/
  AccessTable() {
    return this.query(
      `SELECT table_name, table_comment, table_rows, create_time FROM information_schema.tables WHERE table_schema = 'dxgjadmin'`
    );
  }
  /*
   * 创建表
   *tableName = 表名
   * value 表的内容
   * comment = 注释
   */
  addTable(tableName, value, comment) {
    return this.execute(
      "CREATE TABLE IF NOT EXISTS `" +
        tableName +
        "`(`id` INT UNSIGNED AUTO_INCREMENT," +
        value +
        ',PRIMARY KEY ( `id` ))ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT="' +
        comment +
        '";'
    );
  }

  /*
   *修改注释
   * tableName = 表名
   * text = 注释内容
   */
  ModifyComments(tableName, text) {
    return this.execute(`ALTER TABLE ${tableName} COMMENT '${text}'`);
  }
  /*
   * 修改表名
   * TheTableName = 当前表名
   * TableName = 修改的表名
   */
  ModifyNameOfTheTable(TheTableName, TableName) {
    return this.execute(
      "ALTER TABLE `" + TheTableName + "`  RENAME TO `" + TableName + "`;"
    );
  }
  /*删除表*/
  DropTable(tableName) {
    return this.execute(`DROP TABLE ${tableName};`);
  }

  /*获取个表所有参数*/
  informationSchemaCOLUMNS(table_name) {
    return this.query(
      `SELECT COLUMN_NAME,column_comment,data_type,character_maximum_length,table_name FROM information_schema.COLUMNS WHERE table_name = '${table_name}' AND table_schema = 'dxgjadmin';`
    );
  }
  /*
    *修改参数名称
    table_name 表名
    cs_name 当前参数的名称
    cs_nameF = 修改后的名称
    lx 类型
    zs 注释
    */
  amendParameter(table_name, cs_name, cs_nameF, lx, zs) {
    return this.execute(
      "ALTER TABLE " +
        table_name +
        " CHANGE `" +
        cs_name +
        "` `" +
        cs_nameF +
        "` " +
        lx +
        ' COMMENT "' +
        zs +
        '";'
    );
  }
  /*添加参数*/
  Addparameters(table_name, cs_name, lx, zs) {
    return this.execute(
      "ALTER TABLE " +
        table_name +
        " ADD COLUMN `" +
        cs_name +
        "` " +
        lx +
        ' COMMENT "' +
        zs +
        '";'
    );
  }
  /*删除参数*/

  DeleteTheField(table_name, cs_name) {
    return this.execute(`ALTER TABLE ${table_name} DROP ${cs_name};`);
  }

  getMSgList(fromName, fromNameB) {
    console.log(
      `SELECT dxim.*,userNickname,headPortrait FROM dxim INNER JOIN administrator AS c ON dxim.fromName=c.userNmae WHERE ( (fromName LIKE '${fromName}' AND destName LIKE '${fromNameB}')OR fromName LIKE '${fromNameB}' AND destName LIKE '${fromName}' ) ORDER BY dxim.turnoverTime ASC`
    );
    return this.query(
      `SELECT dxim.*,userNickname,headPortrait FROM dxim INNER JOIN administrator AS c ON dxim.fromName=c.userNmae WHERE ( (fromName LIKE '${fromName}' AND destName LIKE '${fromNameB}')OR fromName LIKE '${fromNameB}' AND destName LIKE '${fromName}' ) ORDER BY dxim.turnoverTime ASC`
    );
  }
  getMaxIds(name) {
    return this
      .query(`SELECT a.* FROM ( SELECT destName AS lxr, d.* FROM dxim d WHERE (d.fromName = '${name}') AND (d.destName <> '${name}')
        UNION 
                SELECT fromName AS lxr, f.* FROM dxim f WHERE (f.destName = '${name}') AND (f.fromName <> '${name}')
ORDER BY turnoverTime DESC) a GROUP BY lxr ORDER BY MAX(a.turnoverTime) DESC;`);
  }
  getUserinfo(name) {
    return this.query(
      `SELECT userNmae,userNickname,headPortrait FROM administrator WHERE(userNmae!="${name}")`
    );
  }

  // pc设备列表
  getequipmentinformation(page, cx) {
    const jhber = [];
    let text = "";
    if (cx.length > 0) {
      text = "WHERE";
      cx.map((a) => {
        jhber.push(a.key + " LIKE " + `'%${a.val}%'`);
      });

      text = text + " " + jhber.join(" AND ");
      console.log(cx);
      console.log(text);
    }
    return this.query(
      `SELECT equipmentinformation.*,axtask.SiteName,axtask.VenueContactPerson,axtask.contactway,axtask.district,axtask.street,axtask.detailedAddress FROM equipmentinformation  INNER JOIN axtask ON equipmentinformation.taskId = axtask.id ${text} LIMIT ${
        (page - 1) * 10
      }, 10`
    );
  }

  // phone勘察列表
  getkanchaLIst(Surveygroup, state, SurveygroupRen, SiteName) {
    // AND (a.state=${state})
    let jnse = "";
    let jnseb = "";
    let jnsec = "";
    if (state) {
      jnse = ` AND a.state=${state}`;
    }
    if (SurveygroupRen) {
      jnseb = ` AND a.SurveygroupRen=${SurveygroupRen}`;
    }
    if (SiteName) {
      console.log(9999999999999);
      jnsec = ` AND a.SiteName LIKE "%${SiteName}%" `;
    }
    console.log(`
    SELECT a.id,a.SiteName,a.VenueContactPerson,a.typeOfSiteSoil,a.contactway,a.state,a.InvestigationTheTime,a.reasonCancellation,a.VenueContactPerson,a.contactway,b.taskAllocation,b.AllocationTime,b.appointmentTraining FROM axtask AS a INNER JOIN task_progress AS b ON a.id = b.taskId WHERE (a.Surveygroup=${Surveygroup} ${jnse} ${jnseb} ${jnsec}) ORDER BY a.id DESC LIMIT 0, 1000`);

    console.log("6666");
    return this.query(
      `
      SELECT a.id,a.SiteName,a.VenueContactPerson,a.typeOfSiteSoil,a.contactway,a.state,a.InvestigationTheTime,a.reasonCancellation,a.VenueContactPerson,a.contactway,b.taskAllocation,b.AllocationTime,b.appointmentTraining FROM axtask AS a INNER JOIN task_progress AS b ON a.id = b.taskId WHERE (a.Surveygroup=${Surveygroup} ${jnse} ${jnseb} ${jnsec}) ORDER BY a.id DESC LIMIT 0, 1000`
    );
  }

  getzs(Surveygroup, state, SurveygroupRen) {
    let jnse = "";
    let jnseb = "";
    if (state) {
      jnse = ` AND a.state=${state}`;
    }
    if (SurveygroupRen) {
      jnseb = ` AND a.SurveygroupRen=${SurveygroupRen}`;
    }
    return this.query(
      `SELECT COUNT(a.id) AS SUM FROM axtask AS a INNER JOIN task_progress AS b ON a.id = b.taskId WHERE (a.Surveygroup=${Surveygroup} ${jnse} ${jnseb})`
    );
  }

  sbglcx(TYPE) {
    return this.query(
      `SELECT COUNT('id') AS num FROM equipmentinformation A INNER JOIN axtask B ON A.taskId = B.id AND TYPE=${TYPE}`
    );
  }

  cyyxcase() {
    return this.query(
      `SELECT id,title,subheading,classify,backgroundImage,NumberOfVisitors,addTime FROM cyyxcase`
    );
  }
  cyyxcaseid(id) {
    return this.query(
      `SELECT id,title,subheading,classify,backgroundImage,NumberOfVisitors,ADDTIME FROM cyyxcase WHERE classify = ${id}`
    );
  }

  cyyxdynamiccondition() {
    return this.query(
      `SELECT id,title,backgroundImage,addTime,NumberOfVisitors FROM cyyxdynamiccondition`
    );
  }
  indexcyyxtemplate() {
    return this.query(
      `SELECT id,remark,zlogo FROM cyyxtemplate ORDER BY id DESC`
    );
  }

  gethncase(TYPE) {
    if (TYPE) {
      return this.query(
        `SELECT id,titlename,cover FROM hncase WHERE TYPE=` + TYPE
      );
    } else {
      return this.query(`SELECT id,titlename,cover FROM hncase`);
    }
  }
  gethncasetj(TYPE) {
    return this.query(`SELECT id,titlename,cover FROM hncase WHERE istj=1`);
  }
  gethnconstructionPlant(TYPE) {
    if (TYPE) {
      return this.query(
        `SELECT id,titlename,cover,year,day,synopsis FROM hnconstructionPlant WHERE TYPE=` +
          TYPE
      );
    } else {
      return this.query(`SELECT id,titlename,cover FROM hnconstructionPlant`);
    }
  }
  gethnconstructionPlantxw() {
    return this.query(
      `SELECT id,titlename,cover,year,day,synopsis FROM hnconstructionPlant WHERE TYPE=3 OR TYPE = 4`
    );
  }

  getxingwen(homet) {
    return this.query(
      `SELECT id,titlename,cover,year,day,synopsis FROM hnconstructionPlant WHERE homet=` +
        homet
    );
  }

  zdseql(key, roleId) {
    let jnse = "";
    console.log("6666666");
    if (roleId) {
      jnse = `AND roleId=${roleId}`;
    }
    return this.query(
      `SELECT * FROM dxDictionarymanagement WHERE zdkey='${key}' ` + jnse
    );
  }

  bogetcp(name, productClassification, roleId) {
    let jnnse = "";
    let jnse = "";
    if (productClassification) {
      jnnse = " AND productClassification LIKE " + productClassification;
    }
    if (roleId) {
      jnse = ` AND roleId=${roleId}`;
    }
    return this.query(
      `SELECT id,name,banner,productClassification FROM botproductList WHERE name LIKE '%${name}%'` +
        jnnse +
        jnse
    );
  }

  getdxcase() {
    return this.query(
      `SELECT id,cover,name,href,type,NumberVisitors,addTime FROM dxcase  ORDER BY paixu DESC`
    );
  }
  getxqsupplierManagement(text, d) {
    let jhhe = "";
    if (d) {
      jhhe = " AND auditStatus=" + d;
    }
    return this.query(
      "SELECT id AS value,NAME AS label FROM `xqsupplierManagement` WHERE ( `scopeServices` LIKE '%" +
        text +
        "%'" +
        jhhe +
        ")"
    );
  }

  getxqsupplierManagementall() {
    return this.query(
      "SELECT id AS value,NAME AS label FROM `xqsupplierManagement`"
    );
  }
  getshmaccount(page, obj) {
    const kkjse = [];
    for (let key in obj) {
      if (key != "sort" && key != "qujian") {
        kkjse.push(`a.${key} = ${obj[key]}`);
      }
    }

    let paixu = "a.id DESC";
    if (obj.sort == 1) {
      paixu = "price DESC";
    }
    if (obj.sort == 2) {
      paixu = "price ASC";
    }
    if (obj.sort == 3) {
      paixu = "NumberVisitors DESC";
    }
    let kkjseStr = kkjse.join(" AND ");
    if (obj.qujian) {
      const jhherr = obj.qujian.split(",");
      kkjseStr =
        kkjseStr + ` AND price >= ${jhherr[0]} AND price <= ${jhherr[1]}`;
    }
    return this.query(
      `SELECT a.id,a.state,b.headPortrait,b.nickname,a.name,a.NumberVisitors,a.system,a.price,a.Numbercollectors,a.describe,a.photograph,a.label,a.accountNumber,a.server FROM shmaccount AS a RIGHT JOIN wxcharUser AS b ON a.userid = b.openid WHERE ( ${kkjseStr} ) ORDER BY ${paixu} LIMIT ${
        (page - 1) * 10
      },10 `
    );
  }

  getshmaccountboj(id) {
    return this.query(
      `SELECT a.id,a.userid,a.state,a.name,a.negotiable,a.NumberVisitors,a.addTime,a.Numbercollectors,b.headPortrait,b.nickname,a.system,a.price,a.Numbercollectors,a.describe,a.photograph,a.label,a.accountNumber,a.server FROM shmaccount AS a RIGHT JOIN wxcharUser AS b ON a.userid = b.openid WHERE ( a.id=${id} ) `
    );
  }

  // 查询60天的数据
  // let webConfig = this.model("webConfig");
  // let arlist = await webConfig.getxstradeDdebtor();
  getxstradeDdebtor(salesman, day, type) {
    let sqlStr = "";
    let px = "";
    if (salesman) {
      const cxe = type == 1 ? " DESC" : " ASC";
      sqlStr = "salesman!=" + salesman + " AND";
      px = " ORDER BY DateDelivery " + cxe;
    }
    return this.query(
      `SELECT * FROM xstradeDdebtor WHERE ${sqlStr} DATE_SUB(CURDATE(), INTERVAL ${
        day || 60
      } DAY) >= DATE(DateDelivery)${px};`
    );
  }

  xzzpchar(fromId, destId) {
    return this.query(
      `SELECT dxim.*,c.head_url,c.name FROM dxim INNER JOIN tb_user AS c ON dxim.fromId=c.mobile WHERE ( (fromId LIKE '${fromId}' AND destId LIKE '${destId}')OR fromId LIKE '${destId}' AND destId LIKE '${fromId}' ) ORDER BY dxim.id ASC`
    );
  }

  xxxdxerf(fromId) {
    return this
      .query(`SELECT d.addTime,d.fromId,d.destId,d.msgBody,u.name,u.head_url,u.id AS uid,CONCAT(fromId,destId) AS fd  FROM dxim d
      INNER JOIN tb_user u ON u.mobile=d.destId
      WHERE 
      d.id  IN(
      SELECT MAX(dx.id) AS gid FROM dxim dx WHERE fromId = '${fromId}' OR destId='${fromId}'  GROUP BY destId 
      ) ORDER BY d.id  DESC`);
  }

  // 随机条数
  getsjwxcharUserHbcj() {
    return this.query(
      `SELECT a.id,a.headimgurl,a.nickname FROM wxcharUser_hbcj AS a ORDER BY RAND() LIMIT 40;`
    );
  }

  gethhtzDataList() {
    return this.query(`SELECT
    order_id,
    order_num,DATE,client_code,mater.id AS material_id, mater.material_code,mater.type_name,mater.commit_date,proc.is_curent,
    proc.produce_manager,proc.prouce_user,
    proc.status,
    mater.sent_task_date,
    proc.appoint_date,
    proc.start_work_date,
    IF(
    TIMESTAMPDIFF(MINUTE,DATE_FORMAT(NOW(),'%Y-%m-%d %H-%i-%s'),DATE_FORMAT(mater.sent_task_date,'%Y-%m-%d %H-%i-%s'))>2*60 AND proc.prouce_user=0 AND proc.is_curent=1,
    1,
      IF(
      TIMESTAMPDIFF(MINUTE,DATE_FORMAT(NOW(),'%Y-%m-%d %H-%i-%s'),DATE_FORMAT(proc.appoint_date,'%Y-%m-%d %H-%i-%s'))>2*60 AND proc.prouce_user!=0  AND proc.is_curent=1
      ,2,3
      )
    ) AS out_type,
    
    IF(TIMESTAMPDIFF(MINUTE,DATE_FORMAT(NOW(),'%Y-%m-%d %H-%i-%s'),DATE_FORMAT(mater.sent_task_date,'%Y-%m-%d %H-%i-%s'))>2*60 AND proc.prouce_user=0 AND proc.is_curent=1 ,
    TIMESTAMPDIFF(MINUTE,DATE_FORMAT(NOW(),'%Y-%m-%d %H-%i-%s'),DATE_FORMAT(mater.sent_task_date,'%Y-%m-%d %H-%i-%s')),
    IF(
      TIMESTAMPDIFF(MINUTE,DATE_FORMAT(NOW(),'%Y-%m-%d %H-%i-%s'),DATE_FORMAT(proc.appoint_date,'%Y-%m-%d %H-%i-%s'))>2*60 AND proc.prouce_user!=0  AND proc.is_curent=1,
      TIMESTAMPDIFF(MINUTE,DATE_FORMAT(NOW(),'%Y-%m-%d %H-%i-%s'),DATE_FORMAT(proc.appoint_date,'%Y-%m-%d %H-%i-%s')),
      TIMESTAMPDIFF(MINUTE,DATE_FORMAT(NOW(),'%Y-%m-%d %H-%i-%s'),DATE_FORMAT(proc.start_work_date,'%Y-%m-%d %H-%i-%s'))
      )
    ) out_time_count,
    
    IF(TIMESTAMPDIFF(MINUTE,DATE_FORMAT(NOW(),'%Y-%m-%d %H-%i-%s'),DATE_FORMAT(mater.sent_task_date,'%Y-%m-%d %H-%i-%s'))>2*60 AND proc.prouce_user=0 AND proc.is_curent=1,proc.produce_manager,proc.prouce_user) AS user_id
      
  FROM
    mould_order order1
    INNER JOIN order_material mater ON order1.id=mater.order_id AND mater.sent_task_date IS NOT NULL
    INNER JOIN material_processes proc ON proc.material_id=mater.id
  
  WHERE  mater.status=1  AND proc.is_curent=1 
  OR  
  (TIMESTAMPDIFF(MINUTE,DATE_FORMAT(NOW(),'%Y-%m-%d %H-%i-%s'),DATE_FORMAT(mater.sent_task_date,'%Y-%m-%d %H-%i-%s'))>2*60 AND proc.prouce_user=0 AND proc.is_curent=1 )
  OR
  (TIMESTAMPDIFF(MINUTE,DATE_FORMAT(NOW(),'%Y-%m-%d %H-%i-%s'),DATE_FORMAT(proc.appoint_date,'%Y-%m-%d %H-%i-%s'))>2*60 AND proc.prouce_user!=0  AND proc.is_curent=1)
  OR
  (TIMESTAMPDIFF(MINUTE,DATE_FORMAT(NOW(),'%Y-%m-%d %H-%i-%s'),DATE_FORMAT(proc.start_work_date,'%Y-%m-%d %H-%i-%s'))>proc.pre_working_hours*60 AND proc.prouce_user!=0  AND proc.is_curent=1
     OR( proc.pre_working_hours IS NULL AND DATEDIFF(proc.commit_date,NOW())>0  AND proc.is_curent=1)
    )
  ORDER BY proc.id DESC`);
  }
};
